/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.User;
import java.sql.*;
import java.util.ArrayList;
import org.apache.log4j.Logger;
import util.DBUtil;

/**
 *
 * @author NHAN
 */
public class UserDAO {

    Connection conn;
    private int totalRecords = 0;
    String sqlSelectByID = "select * from [User] where id=?";
    String sqlSelectByEmail = "select * from [User] where email=?";
    String sqlSelectByActCode="select * from [user] where activation_code=?";
    String sqlReadByUserIDAndPassword = "select * from [User] where Username=? and Password=?";
    String sqlCreate = "insert into [User](first_name,last_name,gender,date_of_birth,email,username,password,role_id,registered_date) values(?, ?, ?, ?,?,?,?,?,?)";
    String sqlUpdate = "update [User] set first_name=?, last_name=?, gender=?, date_of_birth=?,email=?,username=?,password=?,role_id=?,registered_date=? where id=?";
    String sqlUpdateActivationCode = "update [User] set activation_code=? where id=?";
    String sqlUpdatePasswordByActCode = "update [user] set Password=?, Activation_code=? where Activation_Code=?";
    String sqlUpdatePasswordById = "update [user] set Password=?, Activation_code=? where id=?";
    String sqlDelete = "delete from [User] where id=?";
    Logger logger = Logger.getLogger(UserDAO.class);

    public UserDAO() {
        //BasicConfigurator.configure();
    }

    /**
     * get a User by specifying identifier value
     *
     * @param id
     * @return User object
     */
    public User getUser(int id) {
        User u = new User();
        try {
            conn = DBUtil.getConnection();

            if (conn == null) {
                logger.info("Cannot get connection");
                return null;
            }//first_name,last_name,gender,date_of_birth,email,username,password,role_id,registered_date
            PreparedStatement ps = conn.prepareStatement(sqlSelectByID);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                u.setId(id);
                u.setFirstName(rs.getString("first_name"));
                u.setLastName(rs.getString("last_name"));
                u.setGender(rs.getBoolean("gender"));
                u.setDateOfBirth(rs.getDate("date_of_birth"));
                u.setEmail(rs.getString("email"));
                u.setUsername(rs.getString("username"));
                u.setPassword(rs.getString("password"));
                u.setRole(rs.getInt("role_id"));
                u.setRegisteredDate(rs.getDate("registered_date"));
                u.setActivationCode(rs.getString("activation_code"));
            }
            DBUtil.disconnect();

        } catch (SQLException ex) {
            logger.info("SQL error: " + ex.getMessage());
        } catch (Exception ex) {
            logger.info("Error: " + ex.getMessage());
        }
        return u;
    }

    public User getUser(String email) {
        User u = new User();
        try {
            conn = DBUtil.getConnection();

            if (conn == null) {
                logger.info("Cannot get connection");
                return null;
            }//first_name,last_name,gender,date_of_birth,email,username,password,role_id,registered_date
            PreparedStatement ps = conn.prepareStatement(sqlSelectByEmail);
            ps.setString(1, email);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                u.setId(rs.getInt("id"));
                u.setFirstName(rs.getString("first_name"));
                u.setLastName(rs.getString("last_name"));
                u.setGender(rs.getBoolean("gender"));
                u.setDateOfBirth(rs.getDate("date_of_birth"));
                u.setEmail(rs.getString("email"));
                u.setUsername(rs.getString("username"));
                u.setPassword(rs.getString("password"));
                u.setRole(rs.getInt("role_id"));
                u.setRegisteredDate(rs.getDate("registered_date"));
            }
            DBUtil.disconnect();

        } catch (SQLException ex) {
            logger.info("SQL error: " + ex.getMessage());
        } catch (Exception ex) {
            logger.info("Error: " + ex.getMessage());
        }
        return u;
    }

    public ArrayList<User> getUsers(String[] email) {
        ArrayList<User> list = new ArrayList<User>();
        try {
            conn = DBUtil.getConnection();

            if (conn == null) {
                logger.info("Cannot get connection");
                return null;
            }//first_name,last_name,gender,date_of_birth,email,username,password,role_id,registered_date
            String sqlCommand = "select id from [user] where email in (";
            for (int i = 0; i < email.length; i++) {
                if (i == 0) {
                    sqlCommand += ("'" + email[i] + "'");
                } else {
                    sqlCommand += (", '" + email[i] + "'");
                }
            }
            sqlCommand += ")";
            PreparedStatement ps = conn.prepareStatement(sqlCommand);
            //ps.setString(1, email);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                User u = new User();
                u.setId(rs.getInt(1));
                list.add(u);
            }
            DBUtil.disconnect();

        } catch (SQLException ex) {
            logger.info("SQL error: " + ex.getMessage());
        } catch (Exception ex) {
            logger.info("Error: " + ex.getMessage());
        }
        return list;
    }

    /**
     *
     * @param currentPage current page to select
     * @param pageSize number of records per page
     * @return list of Users
     */
    public ArrayList<User> getUsers(int currentPage, int pageSize) {
        ArrayList<User> list = new ArrayList<User>();
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return null;
            }//first_name,last_name,gender,date_of_birth,email,username,password,role_id,registered_date
            QueryObject qo = new QueryObject("[User]", "id", "id");
            qo.setColumns("id, first_name,last_name,gender,date_of_birth,email,username,password,role_id,registered_date");
            qo.setCurrentPage(currentPage);
            qo.setPageSize(pageSize);
            CallableStatement cs = qo.getCallableStatement(conn);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                User u = new User();
                u.setId(rs.getInt(1));
                u.setFirstName(rs.getString(2));
                u.setLastName(rs.getString(3));
                u.setGender(rs.getBoolean(4));
                u.setDateOfBirth(rs.getDate(5));
                u.setEmail(rs.getString(6));
                u.setUsername(rs.getString(7));
                u.setPassword(rs.getString(8));
                u.setRole(rs.getInt(9));
                u.setRegisteredDate(rs.getDate(10));
                list.add(u);
            }
            totalRecords = qo.getTotals();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return list;
    }

    public ArrayList<User> getUsers(int currentPage, int pageSize, String name, int classId) {
        ArrayList<User> list = new ArrayList<User>();
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return null;
            }//first_name,last_name,gender,date_of_birth,email,username,password,role_id,registered_date
            QueryObject qo = new QueryObject("[User]", "id", "id");
            qo.setColumns("[user].id, first_name,last_name,gender,date_of_birth,email,username,password,role_id,registered_date");
            name += " '%" + name + "%' ";
            qo.setWhereClause("first_name+last_name like " + name + "and id in (select student_id from class_student where class_id=" + classId + ")");
            qo.setCurrentPage(currentPage);
            qo.setPageSize(pageSize);
            CallableStatement cs = qo.getCallableStatement(conn);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                User u = new User();
                u.setId(rs.getInt(1));
                u.setFirstName(rs.getString(2));
                u.setLastName(rs.getString(3));
                u.setGender(rs.getBoolean(4));
                u.setDateOfBirth(rs.getDate(5));
                u.setEmail(rs.getString(6));
                u.setUsername(rs.getString(7));
                u.setPassword(rs.getString(8));
                u.setRole(rs.getInt(9));
                u.setRegisteredDate(rs.getDate(10));
                list.add(u);
            }
            totalRecords = qo.getTotals();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return list;
    }

    public ArrayList<User> getUsersByName(int currentPage, int pageSize, String name) {
        ArrayList<User> list = new ArrayList<User>();
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return null;
            }//first_name,last_name,gender,date_of_birth,email,username,password,role_id,registered_date
            QueryObject qo = new QueryObject("[User]", "id", "id");
            qo.setColumns("id, first_name,last_name,gender,date_of_birth,email,username,password,role_id,registered_date");
            name = "%" + name + "%";
            qo.setWhereClause("username like '" + name + "' or email like '" + name + "'");
            qo.setCurrentPage(currentPage);
            qo.setPageSize(pageSize);
            CallableStatement cs = qo.getCallableStatement(conn);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                User u = new User();
                u.setId(rs.getInt(1));
                u.setFirstName(rs.getString(2));
                u.setLastName(rs.getString(3));
                u.setGender(rs.getBoolean(4));
                u.setDateOfBirth(rs.getDate(5));
                u.setEmail(rs.getString(6));
                u.setUsername(rs.getString(7));
                u.setPassword(rs.getString(8));
                u.setRole(rs.getInt(9));
                u.setRegisteredDate(rs.getDate(10));
                list.add(u);
            }
            totalRecords = qo.getTotals();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return list;
    }

    public User getUserByIDAndPassword(String username, String password) {
        User user = new User();
        try {

            conn = DBUtil.getConnection();
            if (conn == null) {
                return user;
            }
            PreparedStatement ps = conn.prepareStatement(sqlReadByUserIDAndPassword);
            ps.setString(1, username);
            ps.setString(2, password);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setFirstName(rs.getString("first_name"));
                user.setLastName(rs.getString("last_name"));
                user.setEmail(rs.getString("email"));
                user.setRole(rs.getInt("role_id"));
                DBUtil.disconnect();
            }

        } catch (SQLException ex) {
            logger.info(ex.getMessage());

        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return user;
    }

    /**
     * add new User
     *
     * @param u User
     * @return greater than 0 value if the action is success, else return 0
     */
    public int create(User u) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return 0;
            }//first_name,last_name,gender,date_of_birth,email,username,password,role_id,registered_date
            PreparedStatement ps = conn.prepareStatement(sqlCreate);
            ps.setString(1, u.getFirstName());
            ps.setString(2, u.getLastName());
            ps.setBoolean(3, u.getGender());
            ps.setDate(4, new java.sql.Date(u.getDateOfBirth().getTime()));
            ps.setString(5, u.getEmail());
            ps.setString(6, u.getUsername());
            ps.setString(7, u.getPassword());
            ps.setInt(8, u.getRole());
            ps.setDate(9, new java.sql.Date(u.getRegisteredDate().getTime()));
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return effectedRows;
    }

    /**
     * update specified User
     *
     * @param u User to update
     * @return 1 or greater if action is successful, else return 0
     */
    public int update(User u) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlUpdate);
            ps.setString(1, u.getFirstName());
            ps.setString(2, u.getLastName());
            ps.setBoolean(3, u.getGender());
            ps.setDate(4, new java.sql.Date(u.getDateOfBirth().getTime()));
            ps.setString(5, u.getEmail());
            ps.setString(6, u.getUsername());
            ps.setString(7, u.getPassword());
            ps.setInt(8, u.getRole());
            ps.setDate(9, new java.sql.Date(u.getRegisteredDate().getTime()));
            ps.setInt(5, u.getId());
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return effectedRows;
    }

    /**
     * update password by specifying activation code
     * @param u
     * @return 
     */
    public int updatePassword(User u, boolean useActivationCode) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return 0;
            }
            PreparedStatement ps;
            if (useActivationCode) {
                ps = conn.prepareStatement(sqlUpdatePasswordByActCode);
                ps.setString(1, u.getPassword());
                ps.setString(2, "");
                ps.setString(3, u.getActivationCode());
            } else {
                ps = conn.prepareStatement(sqlUpdatePasswordById);
                ps.setString(1, u.getPassword());
                ps.setString(2, "");
                ps.setInt(3, u.getId());
            }
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return effectedRows;
    }

    /**
     * update activation code
     * @param u
     * @return 
     */
    public int updateActivationCode(User u) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlUpdateActivationCode);
            ps.setString(1, u.getActivationCode());
            ps.setInt(2, u.getId());
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return effectedRows;
    }

    /**
     * delete a User by specifying identifier value
     *
     * @param id
     * @return 1 or greater value if action is successful , else return 0
     */
    public int delete(int id) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlDelete);
            ps.setInt(1, id);
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return effectedRows;
    }

    /**
     * @return the totalRecords
     */
    public int getTotalRecords() {
        return totalRecords;
    }

    /**
     * @param totalRecords the totalRecords to set
     */
    public void setTotalRecords(int totalRecords) {
        this.totalRecords = totalRecords;
    }
}